<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=2">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">
  <link rel="apple-touch-icon" sizes="180x180" href="/myblog/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/myblog/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/myblog/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/myblog/images/logo.svg" color="#222">

<link rel="stylesheet" href="/myblog/css/main.css">


<link rel="stylesheet" href="/myblog/lib/font-awesome/css/all.min.css">
  <link rel="stylesheet" href="/myblog/lib/pace/pace-theme-minimal.min.css">
  <script src="/myblog/lib/pace/pace.min.js"></script>

<script id="hexo-configurations">
    var NexT = window.NexT || {};
    var CONFIG = {"hostname":"gao-qianwei.gitee.io","root":"/myblog/","scheme":"Gemini","version":"7.8.0","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12,"onmobile":false},"copycode":{"enable":false,"show_result":false,"style":null},"back2top":{"enable":true,"sidebar":false,"scrollpercent":false},"bookmark":{"enable":false,"color":"#222","save":"auto"},"fancybox":false,"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"algolia":{"hits":{"per_page":10},"labels":{"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}},"localsearch":{"enable":true,"trigger":"auto","top_n_per_article":1,"unescape":false,"preload":false},"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},"path":"search.xml"};
  </script>

  <meta name="description" content="一、简介Data warehouse（可简写为DW或者DWH）数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它是一整套包括了etl、调度、建模在内的完整的理论体系。数据仓库的方案建设的目的，是为前端查询和分析作为基础，主要应用于OLAP（on-line Analytical Processing），支持复杂的分析操作，侧重决策支持，听且提供直观易懂的查询结">
<meta property="og:type" content="article">
<meta property="og:title" content="数据仓库基本架构">
<meta property="og:url" content="https://gao-qianwei.gitee.io/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/index.html">
<meta property="og:site_name" content="学无止境">
<meta property="og:description" content="一、简介Data warehouse（可简写为DW或者DWH）数据仓库，是在数据库已经大量存在的情况下，为了进一步挖掘数据资源、为了决策需要而产生的，它是一整套包括了etl、调度、建模在内的完整的理论体系。数据仓库的方案建设的目的，是为前端查询和分析作为基础，主要应用于OLAP（on-line Analytical Processing），支持复杂的分析操作，侧重决策支持，听且提供直观易懂的查询结">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://gao-qianwei.gitee.io/myblog/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/1.png">
<meta property="article:published_time" content="2021-09-30T14:59:30.243Z">
<meta property="article:modified_time" content="2021-09-30T15:36:06.464Z">
<meta property="article:author" content="高乾威">
<meta property="article:tag" content="数据研发">
<meta property="article:tag" content="数据仓库">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://gao-qianwei.gitee.io/myblog/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/1.png">

<link rel="canonical" href="https://gao-qianwei.gitee.io/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/">


<script id="page-configurations">
  // https://hexo.io/docs/variables.html
  CONFIG.page = {
    sidebar: "",
    isHome : false,
    isPost : true,
    lang   : 'zh-CN'
  };
</script>

  <title>数据仓库基本架构 | 学无止境</title>
  






  <noscript>
  <style>
  .use-motion .brand,
  .use-motion .menu-item,
  .sidebar-inner,
  .use-motion .post-block,
  .use-motion .pagination,
  .use-motion .comments,
  .use-motion .post-header,
  .use-motion .post-body,
  .use-motion .collection-header { opacity: initial; }

  .use-motion .site-title,
  .use-motion .site-subtitle {
    opacity: initial;
    top: initial;
  }

  .use-motion .logo-line-before i { left: initial; }
  .use-motion .logo-line-after i { right: initial; }
  </style>
</noscript>

</head>

<body itemscope itemtype="http://schema.org/WebPage">
  <div class="container use-motion">
    <div class="headband"></div>

    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏">
      <span class="toggle-line toggle-line-first"></span>
      <span class="toggle-line toggle-line-middle"></span>
      <span class="toggle-line toggle-line-last"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/myblog/" class="brand" rel="start">
      <span class="logo-line-before"><i></i></span>
      <h1 class="site-title">学无止境</h1>
      <span class="logo-line-after"><i></i></span>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
        <i class="fa fa-search fa-fw fa-lg"></i>
    </div>
  </div>
</div>




<nav class="site-nav">
  <ul id="menu" class="main-menu menu">
        <li class="menu-item menu-item-home">

    <a href="/myblog/" rel="section"><i class="fa fa-home fa-fw"></i>首页</a>

  </li>
        <li class="menu-item menu-item-about">

    <a href="/myblog/about/" rel="section"><i class="fa fa-user fa-fw"></i>关于</a>

  </li>
        <li class="menu-item menu-item-tags">

    <a href="/myblog/tags/" rel="section"><i class="fa fa-tags fa-fw"></i>标签</a>

  </li>
        <li class="menu-item menu-item-categories">

    <a href="/myblog/categories/" rel="section"><i class="fa fa-th fa-fw"></i>分类</a>

  </li>
        <li class="menu-item menu-item-archives">

    <a href="/myblog/archives/" rel="section"><i class="fa fa-archive fa-fw"></i>归档</a>

  </li>
        <li class="menu-item menu-item-links">

    <a href="/myblog/links/" rel="section"><i class="fa fa-link fa-fw"></i>友链</a>

  </li>
      <li class="menu-item menu-item-search">
        <a role="button" class="popup-trigger"><i class="fa fa-search fa-fw"></i>搜索
        </a>
      </li>
  </ul>
</nav>



  <div class="search-pop-overlay">
    <div class="popup search-popup">
        <div class="search-header">
  <span class="search-icon">
    <i class="fa fa-search"></i>
  </span>
  <div class="search-input-container">
    <input autocomplete="off" autocapitalize="off"
           placeholder="搜索..." spellcheck="false"
           type="search" class="search-input">
  </div>
  <span class="popup-btn-close">
    <i class="fa fa-times-circle"></i>
  </span>
</div>
<div id="search-result">
  <div id="no-result">
    <i class="fa fa-spinner fa-pulse fa-5x fa-fw"></i>
  </div>
</div>

    </div>
  </div>

</div>
    </header>

    
  <div class="back-to-top">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>


    <main class="main">
      <div class="main-inner">
        <div class="content-wrap">
          

          <div class="content post posts-expand">
            

    
  
  
  <article itemscope itemtype="http://schema.org/Article" class="post-block" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://gao-qianwei.gitee.io/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/myblog/images/avatar.png">
      <meta itemprop="name" content="高乾威">
      <meta itemprop="description" content="报我楼成秋望月，把君诗读夜回灯">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="学无止境">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          数据仓库基本架构
        </h1>

        <div class="post-meta">

          
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-calendar"></i>
              </span>
              <span class="post-meta-item-text">发表于</span>
              

              <time title="创建时间：2021-09-30 22:59:30 / 修改时间：23:36:06" itemprop="dateCreated datePublished" datetime="2021-09-30T22:59:30+08:00">2021-09-30</time>
            </span>
            <span class="post-meta-item">
              <span class="post-meta-item-icon">
                <i class="far fa-folder"></i>
              </span>
              <span class="post-meta-item-text">分类于</span>
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/myblog/categories/%E6%95%B0%E6%8D%AE%E7%A0%94%E5%8F%91/" itemprop="url" rel="index"><span itemprop="name">数据研发</span></a>
                </span>
                  ，
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/myblog/categories/%E6%95%B0%E6%8D%AE%E7%A0%94%E5%8F%91/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93/" itemprop="url" rel="index"><span itemprop="name">数据仓库</span></a>
                </span>
            </span>

          
            <span class="post-meta-item" title="阅读次数" id="busuanzi_container_page_pv" style="display: none;">
              <span class="post-meta-item-icon">
                <i class="fa fa-eye"></i>
              </span>
              <span class="post-meta-item-text">阅读次数：</span>
              <span id="busuanzi_value_page_pv"></span>
            </span>
            <span class="post-meta-divider">|</span>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">

      
        <h3 id="一、简介"><a href="#一、简介" class="headerlink" title="一、简介"></a>一、简介</h3><p>Data warehouse（可简写为DW或者DWH）数据仓库，是在数据库已经大量存在的情况下，为了进一步<strong>挖掘数据资源、为了决策需要</strong>而产生的，它是<strong>一整套包括了etl、调度、建模在内的完整的理论体系</strong>。数据仓库的方案建设的目的，是为前端查询和分析作为基础，主要应用于OLAP（on-line Analytical Processing），支持复杂的分析操作，侧重决策支持，听且提供直观易懂的查询结果。比较流行的有：AWS Redshift，Greenplum，Hive等。</p>
<span id="more"></span>

<h3 id="二、主要特点"><a href="#二、主要特点" class="headerlink" title="二、主要特点"></a>二、主要特点</h3><ul>
<li>面向主题<ul>
<li>操作型数据库组织面向事务处理任务，而数据仓库中的数据是按照一定的主题域进行组织。</li>
<li>主题是指用户使用数据仓库进行决策时所关心的重点方面，<strong>一个主题通过与多个操作型信息系统相关</strong>。</li>
</ul>
</li>
<li>集成<ul>
<li>需要对源数据进行加工与融合，统一与综合</li>
<li>在加工的过程中必须消除源数据的不一致性，以保证数据仓库内的信息时关于整个企业的一致的全局信息。（关联关系）</li>
</ul>
</li>
<li>不可修改<ul>
<li>DW中的数据并不是最新的，而是来源于其他数据源</li>
<li>数据仓库主要是为决策分析提供数据，涉及的操作主要是数据的查询</li>
</ul>
</li>
<li>与时间相关<ul>
<li>处于决策的需要数据仓库中的数据都需要标明时间属性</li>
</ul>
</li>
</ul>
<h3 id="三、与数据库的对比"><a href="#三、与数据库的对比" class="headerlink" title="三、与数据库的对比"></a>三、与数据库的对比</h3><ul>
<li>DW：专门为数据分析设计的，涉及读取大量数据以了解数据之间的关系和趋势</li>
<li>数据库：用于捕获和存储数据</li>
</ul>
<table>
<thead>
<tr>
<th>特性</th>
<th>数据仓库</th>
<th>事务数据库</th>
</tr>
</thead>
<tbody><tr>
<td>适合的工作负载</td>
<td>分析、报告、大数据</td>
<td>事务处理</td>
</tr>
<tr>
<td>数据源</td>
<td>从多个来源收集和标准化的数据</td>
<td>从单个来源（例如事务系统）捕获的数据</td>
</tr>
<tr>
<td>数据捕获</td>
<td>批量写入操作通过按照预定的批处理计划执行</td>
<td>针对连续写入操作进行了优化，因为新数据能够最大程度地提高事务吞吐量</td>
</tr>
<tr>
<td>数据标准化</td>
<td>非标准化schema，例如星型Schema或雪花型schema</td>
<td>高度标准化的静态schema</td>
</tr>
<tr>
<td>数据存储</td>
<td>使用列式存储进行了优化，可实现轻松访问和高速查询性能</td>
<td>针对在单行型物理块中执行高吞吐量写入操作进行了优化</td>
</tr>
<tr>
<td>数据访问</td>
<td>为最小化I/O并最大化数据吞吐量进行了优化</td>
<td>大量小型读取操作</td>
</tr>
</tbody></table>
<h3 id="四、基本架构"><a href="#四、基本架构" class="headerlink" title="四、基本架构"></a>四、基本架构</h3><p>数据仓库的基本架构主要包含的是数据流入流出的过程，可以分为三层——<strong>数据获取、数据仓库、数据应用：</strong></p>
<img src="/myblog/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84/1.png" alt="img" style="zoom: 50%;">

<p>​                                                                            数据仓库的结构</p>
<p><strong>数据仓库从各数据源获取数据及在数据仓库内的数据转换和流动都可以认为是ETL（抽取Extra, 转化Transfer, 装载Load）的过程。</strong>ETL是数据仓库的流水线，也可以认为是数据仓库的血液，它维系着数据仓库中数据的新陈代谢，而数据仓库日常的管理和维护工作的大部分精力就是保持ETL的正常和稳定。</p>

    </div>

    
    
    

      <footer class="post-footer">
          <div class="post-tags">
              <a href="/myblog/tags/%E6%95%B0%E6%8D%AE%E7%A0%94%E5%8F%91/" rel="tag"># 数据研发</a>
              <a href="/myblog/tags/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93/" rel="tag"># 数据仓库</a>
          </div>

        


        
    <div class="post-nav">
      <div class="post-nav-item">
    <a href="/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E4%BB%93%E5%BA%93%EF%BC%9A%E5%88%86%E5%B1%82/" rel="prev" title="数据仓库分层">
      <i class="fa fa-chevron-left"></i> 数据仓库分层
    </a></div>
      <div class="post-nav-item">
    <a href="/myblog/2021/09/30/%E6%95%B0%E6%8D%AE%E7%A0%94%E5%8F%91%EF%BC%9A%E5%87%A0%E7%A7%8D%E4%B8%8D%E5%90%8C%E5%B2%97%E4%BD%8D/" rel="next" title="大数据相关岗位介绍">
      大数据相关岗位介绍 <i class="fa fa-chevron-right"></i>
    </a></div>
    </div>
      </footer>
    
  </article>
  
  
  



          </div>
          

<script>
  window.addEventListener('tabs:register', () => {
    let { activeClass } = CONFIG.comments;
    if (CONFIG.comments.storage) {
      activeClass = localStorage.getItem('comments_active') || activeClass;
    }
    if (activeClass) {
      let activeTab = document.querySelector(`a[href="#comment-${activeClass}"]`);
      if (activeTab) {
        activeTab.click();
      }
    }
  });
  if (CONFIG.comments.storage) {
    window.addEventListener('tabs:click', event => {
      if (!event.target.matches('.tabs-comment .tab-content .tab-pane')) return;
      let commentClass = event.target.classList[1];
      localStorage.setItem('comments_active', commentClass);
    });
  }
</script>

        </div>
          
  
  <div class="toggle sidebar-toggle">
    <span class="toggle-line toggle-line-first"></span>
    <span class="toggle-line toggle-line-middle"></span>
    <span class="toggle-line toggle-line-last"></span>
  </div>

  <aside class="sidebar">
    <div class="sidebar-inner">

      <ul class="sidebar-nav motion-element">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <!--noindex-->
      <div class="post-toc-wrap sidebar-panel">
          <div class="post-toc motion-element"><ol class="nav"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%80%E3%80%81%E7%AE%80%E4%BB%8B"><span class="nav-text">一、简介</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%BA%8C%E3%80%81%E4%B8%BB%E8%A6%81%E7%89%B9%E7%82%B9"><span class="nav-text">二、主要特点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E4%B8%89%E3%80%81%E4%B8%8E%E6%95%B0%E6%8D%AE%E5%BA%93%E7%9A%84%E5%AF%B9%E6%AF%94"><span class="nav-text">三、与数据库的对比</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E5%9B%9B%E3%80%81%E5%9F%BA%E6%9C%AC%E6%9E%B6%E6%9E%84"><span class="nav-text">四、基本架构</span></a></li></ol></div>
      </div>
      <!--/noindex-->

      <div class="site-overview-wrap sidebar-panel">
        <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
    <img class="site-author-image" itemprop="image" alt="高乾威"
      src="/myblog/images/avatar.png">
  <p class="site-author-name" itemprop="name">高乾威</p>
  <div class="site-description" itemprop="description">报我楼成秋望月，把君诗读夜回灯</div>
</div>
<div class="site-state-wrap motion-element">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
          <a href="/myblog/archives/">
        
          <span class="site-state-item-count">91</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
            <a href="/myblog/categories/">
          
        <span class="site-state-item-count">27</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
            <a href="/myblog/tags/">
          
        <span class="site-state-item-count">59</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author motion-element">
      <span class="links-of-author-item">
        <a href="http://github.com/GaoQianwei" title="GitHub → http:&#x2F;&#x2F;github.com&#x2F;GaoQianwei" rel="noopener" target="_blank">GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="mailto:2966807184@qq.com" title="E-Mail → mailto:2966807184@qq.com" rel="noopener" target="_blank">E-Mail</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://www.zhihu.com/people/xue-wu-zhi-jing-75-22" title="知乎 → https:&#x2F;&#x2F;www.zhihu.com&#x2F;people&#x2F;xue-wu-zhi-jing-75-22" rel="noopener" target="_blank">知乎</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://blog.csdn.net/weixin_45341339" title="CSDN → https:&#x2F;&#x2F;blog.csdn.net&#x2F;weixin_45341339" rel="noopener" target="_blank">CSDN</a>
      </span>
  </div>



      </div><div>
  <canvas id="canvasDiyBlock" style="width:60%;">当前浏览器不支持canvas，请更换浏览器后再试</canvas>
<script src="/myblog/js/custom/clock.js"></script>

</div>


    </div>
  </aside>
  <div id="sidebar-dimmer"></div>


      </div>
    </main>

    <footer class="footer">
      <div class="footer-inner">
        

        

<div class="copyright">
  
  &copy; 2021-04 到 
  <span itemprop="copyrightYear">2022</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">高乾威</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" class="theme-link" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.org/" class="theme-link" rel="noopener" target="_blank">NexT.Gemini</a> 强力驱动
  </div>

<span class="post-meta-divider">|</span>

<div class="theme-info">
  <div class="powered-by"></div>
  <span class="post-count">全站共 117.2k 字</span>
</div>

<span class="post-meta-divider">|</span>
        
<div class="busuanzi-count">
  <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>
</div>








      </div>
    </footer>
  </div>

  
  <script src="/myblog/lib/anime.min.js"></script>
  <script src="/myblog/lib/velocity/velocity.min.js"></script>
  <script src="/myblog/lib/velocity/velocity.ui.min.js"></script>

<script src="/myblog/js/utils.js"></script>

<script src="/myblog/js/motion.js"></script>


<script src="/myblog/js/schemes/pisces.js"></script>


<script src="/myblog/js/next-boot.js"></script>


  <script defer src="/myblog/lib/three/three.min.js"></script>
    <script defer src="/myblog/lib/three/canvas_lines.min.js"></script>


  




  
<script src="/myblog/js/local-search.js"></script>













  

  

</body>
</html>
